with order_today as (
    select
         ID
        ,INPUT_TIME
        ,MAIL_NO
        ,update_time
        ,dt --  时间分区
    FROM jms_ods.yl_oms_order_tb_exporder_hi
    WHERE dt = '{{ execution_date | cst_hour }}'
    and substr(input_time,1,10) >= '{{ execution_date | date_add(-15) | cst_ds }}'
),
order_history as(
    select
        ID
       ,INPUT_TIME
       ,MAIL_NO
       ,update_time
       ,dt --  时间分区
        from jms_dwd.dwd_yl_oms_order_tb_exporder_hi
    where dt >= concat('{{ execution_date | date_add(-15) | cst_ds }}','-00')
    AND dt <= '{{ execution_date | hour_add(-1) | cst_hour }}'
),

order_union as (
    select * from
(
    select * ,row_number() over (PARTITION BY id ORDER BY dt DESC) as rnk
    from (
        select * from order_history
        union all
        select * from order_today
    )  order_mid) t where t.rnk=1
)

INSERT OVERWRITE TABLE jms_dwd.dwd_yl_oms_order_tb_exporder_hi PARTITION (dt)
select
     ID
    ,INPUT_TIME
    ,MAIL_NO
    ,update_time
    ,dt --  时间分区
FROM order_union
distribute by dt,1;